package com.epoint.webapp.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.epoint.webapp.dao.BlogDAO;
import com.epoint.webapp.entity.Blog;
import com.epoint.webapp.util.DateConversion;

public class BlogDAOImpl implements BlogDAO{
	private DataSource dataSource;
	private Connection conn = null ;
	private ResultSet rs = null ;
	private PreparedStatement smt = null ;
	
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public void insert(Blog blog) {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO blog(blogTitle,blogContent,blogCreateDate) "
				+ "VALUES(?,?,NOW())";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, blog.getTitle());
			smt.setString(2, blog.getContent());
			smt.executeUpdate();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	public void update(Blog blog) {
		// TODO Auto-generated method stub
		String sql = "UPDATE blog SET blogTitle=?, blogContent=? WHERE blogID = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, blog.getTitle());
			smt.setString(2, blog.getContent());
			smt.setInt(3, blog.getId());
			smt.executeUpdate();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	public void delete(Blog blog) {
		// TODO Auto-generated method stub
		String sql = "DELETE FROM blog WHERE blogID = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, blog.getId());
			smt.executeUpdate();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	public List<Blog> getList(int start, int size) {
		// TODO Auto-generated method stub
		DateConversion dateConversion = new DateConversion();
		List<Blog> blogList = new ArrayList<Blog>();
		String sql = "SELECT *,(SELECT COUNT(*) FROM blog_message b WHERE a.blogID = b.blogID) AS countMessage, "
				+ "(SELECT COUNT(*) FROM blog_message c WHERE a.blogID = c.blogID AND c.blogMessageResponse = '') AS countNewMessage "
				+ "FROM blog a ORDER BY blogCreateDate DESC LIMIT ?,?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, start);
			smt.setInt(2, size);
			rs = smt.executeQuery();
			while(rs.next()){
				Blog blog = new Blog();
				blog.setId(rs.getInt("blogID"));
				blog.setTitle(rs.getString("blogTitle"));
				blog.setContent(rs.getString("blogContent"));
				blog.setDate(rs.getDate("blogCreateDate"));
				blog.setDateString(dateConversion.ConversionYMD(blog.getDate()));
				blog.setCountMessage(rs.getInt("countMessage"));
				blog.setCountNewMessage(rs.getInt("countNewMessage"));
				blogList.add(blog);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return blogList;
	}

	public Blog get(Blog blog) {
		// TODO Auto-generated method stub
		DateConversion dateConversion = new DateConversion();
		String sql = "SELECT * FROM blog WHERE blogID = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, blog.getId());
			rs = smt.executeQuery();
			if(rs.next()){
				blog.setTitle(rs.getString("blogTitle"));
				blog.setContent(rs.getString("blogContent"));
				blog.setDate(rs.getDate("blogCreateDate"));
				blog.setDateString(dateConversion.ConversionYMD(blog.getDate()));
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return blog;
	}

	public int countTotal() {
		// TODO Auto-generated method stub
		int countTotal = 0;
		String sql = "SELECT COUNT(*) AS countTotal FROM blog";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			rs = smt.executeQuery();
			if(rs.next()){
				countTotal = rs.getInt("countTotal");
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return countTotal;
	}

	

}
